package Dao;

import Model.Bill;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BillDao {
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bill", "root", "123456");
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    public static void release(PreparedStatement pstmt, Connection conn){
        release(null,pstmt,conn);
    }
    public static void release(ResultSet rs, PreparedStatement pstmt, Connection conn){
        try{
            if(rs!=null){
                rs.close();
            }
            if(pstmt!=null){
                pstmt.close();
            }
            if(conn!=null)
            {
                conn.close();
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
    public static List<Bill> select(Bill model) {
        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement stat = null;
        List<Bill> records = new ArrayList<>();
        try {
            conn = getConnection(); // 假设你有一个方法获取数据库连接
            StringBuilder sql = new StringBuilder("SELECT id, date, photo_url, item_name, seller, purchaser, money FROM bill WHERE user_id = ?");
            List<Object> params = new ArrayList<>();
            params.add(model.getUserId());

            if (model.getStartDate() != null && model.getEndDate() != null) {
                sql.append(" AND date BETWEEN ? AND ?");
                // 转换日期格式
                String startDate = convertDateFormat(model.getStartDate());
                String endDate = convertDateFormat(model.getEndDate());
                params.add(startDate);
                params.add(endDate);
            }

            // 添加其他字段的筛选条件
            if (model.getItemName() != null) {
                sql.append(" AND item_name LIKE ?");
                params.add("%" + model.getItemName() + "%");
            }
            if (model.getSeller() != null) {
                sql.append(" AND seller LIKE ?");
                params.add("%" + model.getSeller() + "%");
            }
            if (model.getPurchaser() != null) {
                sql.append(" AND purchaser LIKE ?");
                params.add("%" + model.getPurchaser() + "%");
            }
            if (model.getMoney() != null) {
                sql.append(" AND money = ?");
                params.add(model.getMoney());
            }

            stat = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                stat.setObject(i + 1, params.get(i));
            }

            rs = stat.executeQuery();
            while (rs.next()) {
                Bill bill = new Bill(
                        rs.getInt("id"),
                        rs.getString("date"),
                        rs.getString("photo_url"),
                        rs.getString("item_name"),
                        rs.getString("seller"),
                        rs.getString("purchaser"),
                        rs.getBigDecimal("money")
                );
                records.add(bill);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(rs, stat, conn); // 假设你有一个方法释放资源
        }
        return records;
    }
    public static List<Bill> adminselect(Bill model) {
        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement stat = null;
        List<Bill> records = new ArrayList<>();
        try {
            conn = getConnection(); // 假设你有一个方法获取数据库连接
            StringBuilder sql = new StringBuilder("SELECT id, user_id,date, photo_url, item_name, seller, purchaser, money FROM bill WHERE 1 = 1");
            List<Object> params = new ArrayList<>();

            if (model.getStartDate() != null && model.getEndDate() != null) {
                sql.append(" AND date BETWEEN ? AND ?");
                // 转换日期格式
                String startDate = convertDateFormat(model.getStartDate());
                String endDate = convertDateFormat(model.getEndDate());
                params.add(startDate);
                params.add(endDate);
            }

            // 添加其他字段的筛选条件
            if (model.getItemName() != null) {
                sql.append(" AND item_name LIKE ?");
                params.add("%" + model.getItemName() + "%");
            }
            if (model.getSeller() != null) {
                sql.append(" AND seller LIKE ?");
                params.add("%" + model.getSeller() + "%");
            }
            if (model.getPurchaser() != null) {
                sql.append(" AND purchaser LIKE ?");
                params.add("%" + model.getPurchaser() + "%");
            }
            if (model.getMoney() != null) {
                sql.append(" AND money = ?");
                params.add(model.getMoney());
            }

            stat = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                stat.setObject(i + 1, params.get(i));
            }

            rs = stat.executeQuery();
            while (rs.next()) {
                Bill bill = new Bill(
                        rs.getInt("id"),
                        rs.getInt("user_id"),
                        rs.getString("date"),
                        rs.getString("photo_url"),
                        rs.getString("item_name"),
                        rs.getString("seller"),
                        rs.getString("purchaser"),
                        rs.getBigDecimal("money")
                );
                records.add(bill);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(rs, stat, conn); // 假设你有一个方法释放资源
        }
        return records;
    }
    public static Map<String, Object> selectNum(Bill model) {
        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement stat = null;
        Map<String, Object> result = new HashMap<>();
        try {
            conn = getConnection(); // 假设你有一个方法获取数据库连接
            StringBuilder sql = new StringBuilder("SELECT COUNT(*) AS total_records, SUM(money) AS total_amount FROM bill WHERE 1=1");
            List<Object> params = new ArrayList<>();
            if (model.getStartDate() != null && model.getEndDate() != null) {
                sql.append(" AND date BETWEEN ? AND ?");
                // 转换日期格式
                String startDate = convertDateFormat(model.getStartDate());
                String endDate = convertDateFormat(model.getEndDate());
                params.add(startDate);
                params.add(endDate);
            }

            // 添加其他字段的筛选条件
            if (model.getItemName() != null) {
                sql.append(" AND item_name LIKE ?");
                params.add("%" + model.getItemName() + "%");
            }
            if (model.getSeller() != null) {
                sql.append(" AND seller LIKE ?");
                params.add("%" + model.getSeller() + "%");
            }
            if (model.getPurchaser() != null) {
                sql.append(" AND purchaser LIKE ?");
                params.add("%" + model.getPurchaser() + "%");
            }
            if (model.getMoney() != null) {
                sql.append(" AND money = ?");
                params.add(model.getMoney());
            }

            stat = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                stat.setObject(i + 1, params.get(i));
            }

            rs = stat.executeQuery();
            if (rs.next()) {
                result.put("total_records", rs.getInt("total_records"));
                result.put("total_amount", rs.getBigDecimal("total_amount"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(rs, stat, conn); // 假设你有一个方法释放资源
        }
        return result;
    }
    // 日期格式转换方法
    public static String convertDateFormat(String dateStr) {
        // 定义可能的输入格式
        DateTimeFormatter[] inputFormatters = {
                DateTimeFormatter.ofPattern("yyyy/MM/dd"), // 格式：2025/03/01
                DateTimeFormatter.ofPattern("MM/dd/yyyy"), // 格式：03/01/2025
                DateTimeFormatter.ofPattern("yyyy-MM-dd")  // 格式：2025-03-01
        };

        // 定义输出格式（统一为 yyyy-MM-dd）
        DateTimeFormatter outputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

        for (DateTimeFormatter formatter : inputFormatters) {
            try {
                // 尝试用当前格式解析日期
                LocalDate date = LocalDate.parse(dateStr, formatter);
                return date.format(outputFormatter); // 转换为统一的 yyyy-MM-dd 格式
            } catch (DateTimeParseException e) {
                // 如果当前格式解析失败，尝试下一个格式
                continue;
            }
        }
        // 如果所有格式都解析失败，抛出异常或返回原始字符串
        throw new IllegalArgumentException("Invalid date format: " + dateStr);
    }
    public static Integer selectNewID() {
        ResultSet rs = null;
        PreparedStatement stat = null;
        int a = 0;
        Connection conn = getConnection();
        try {
            String sql = "select Max(id) from bill";
            stat = conn.prepareStatement(sql);
            rs = stat.executeQuery();
            if(rs.next()) {
                int maxId = rs.getInt(1);
                a = maxId + 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(rs, stat, conn);
        }
        return a;
    }
    public static boolean insert(Bill model) {
        boolean flag=false;
        try {
            String sql = "insert into bill(id,user_id,date,photo_url,item_name,seller,purchaser,money) values(?,?,?,?,?,?,?,?);";
            Connection conn = getConnection();
            PreparedStatement stat = conn.prepareStatement(sql);
            stat.setInt(1, model.getId());
            stat.setInt(2, model.getUserId());
            stat.setString(3, model.getDate());
            stat.setString(4, model.getUrl());
            stat.setString(5, model.getItemName());
            stat.setString(6, model.getSeller());
            stat.setString(7, model.getPurchaser());
            stat.setBigDecimal(8, model.getMoney());
            stat.executeUpdate();
            release(stat, conn);
            flag=true;
            return flag;
        } catch (SQLException e) {
            e.printStackTrace();
            return flag;
        }
    }
}
